﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net.Mail;
using System.Net;
namespace Sylvain.Model
{
    public class SylvainModel:ISylvainModel
    {
       //Instance of SylvainData Class of Database
        SylvainDataDataContext sylvainDataContext = new SylvainDataDataContext();

        #region Common Page Methods 

        /// <summary>
         /// Insert Page Content
         /// </summary>
         /// <param name="PageId">Page Id</param>
         /// <param name="PageContent">Page Content</param>
         /// <param name="UpdatedBy">Updated By</param>
        public void InsertPageContent(int PageId, string PageContent, string UpdatedBy)
        {
            PageDetail pageDetail = new PageDetail
                                        {
                                            PageDescription = PageContent,
                                            PageMasterId = PageId,
                                            UpdatedBy = UpdatedBy,
                                            Status = true,
                                            DateCreated = System.DateTime.Now
                                        };

            sylvainDataContext.PageDetails.InsertOnSubmit(pageDetail);
            sylvainDataContext.SubmitChanges();
        }
        /// <summary>
        /// Get Page Content
        /// </summary>
        /// <param name="PageId">Page Id</param>
        /// <returns>Page Content</returns>
        public string GetPageContent(int PageId)
        {
            var objItem = sylvainDataContext.PageDetails.Where(x => x.PageMasterId == PageId);
            if (objItem.Count()>0)
                return objItem.OrderByDescending(x => x.DateCreated).FirstOrDefault().PageDescription;
            else
                return "";
        }
        /// <summary>
        /// Get Page Id
        /// </summary>
        /// <param name="PageHandler">Page Handler</param>
        /// <returns>Page Id</returns>
        public int GetPageId(string PageHandler)
        {
            if (PageHandler != null)
            {
                return sylvainDataContext.PageMasters.FirstOrDefault(x => x.PageHandler == PageHandler).PageId;
            }
            else
            {
                return 0;
            }
        }

        #endregion

        #region Contract

        /// <summary>
        /// Add Contract
        /// </summary>
        /// <param name="objContract">Object of Contract</param>
        public void AddContract(Contract objContract)
        {
            sylvainDataContext.Contracts.InsertOnSubmit(objContract);
            sylvainDataContext.SubmitChanges();
        }
        /// <summary>
        /// Delete Contract
        /// </summary>
        /// <param name="ContractId">Contract Id</param>
        public Contract DeleteContract(int ContractId)
        {
            var contractRow = from objContract in sylvainDataContext.Contracts
                              where objContract.Id == ContractId
                              select objContract;
            Contract obj = contractRow.FirstOrDefault();
            sylvainDataContext.Contracts.DeleteAllOnSubmit(contractRow);            
            sylvainDataContext.SubmitChanges();
            return obj;
        }
        /// <summary>
        /// Edit Contract
        /// </summary>
        /// <param name="objContract">Object of Contract</param>
        public void EditContract(Contract objContract)
        {
            Contract updateContract = sylvainDataContext.Contracts.FirstOrDefault(u => u.Id == objContract.Id);
            if (updateContract != null)
            {
                updateContract.ClientName = objContract.ClientName;
                updateContract.Description = objContract.Description;
                updateContract.Title = objContract.Title;
                if (objContract.Image != null && objContract.Image != "")
                {
                    updateContract.Image = objContract.Image;
                }
                updateContract.DateCreated = objContract.DateCreated;
                
            };

            sylvainDataContext.SubmitChanges();
        
        }
        /// <summary>
        /// Get List of all Contracts
        /// </summary>
        /// <returns>List of Contracts</returns>
        public List<Contract> GetAllContracts()
        {

            return sylvainDataContext.Contracts.ToList();
        }
        /// <summary>
        /// Get Contract Description
        /// </summary>
        /// <param name="id">Contract Id</param>
        /// <returns>Contract Description</returns>
        public Contract GetContractDescription(int id)
        {
            return sylvainDataContext.Contracts.FirstOrDefault(p => p.Id == id);
        }
        
        #endregion

        #region News Section
        /// <summary>
        /// Display all Records for NewsSection in Gridview
        /// </summary>
        /// <returns>List of News</returns>
        public List<NewsSection> GetAllNews()
        {
            return sylvainDataContext.NewsSections.OrderByDescending(x=>x.CreatedDate).ToList();
        }
        /// <summary>
        /// Add Record in News Section 
        /// </summary>
        /// <param name="objNewsSection">Object of NewsSection</param>
        public void AddNewsSection(NewsSection objNewsSection)
        {
            sylvainDataContext.NewsSections.InsertOnSubmit(objNewsSection);
            sylvainDataContext.SubmitChanges();
        }
        /// <summary>
        /// Delete Record in News Section
        /// </summary>
        /// <param name="Id">News Id</param>
        public void DeleteNewsSection(int Id)
        {
            var NewsRow = from obj in sylvainDataContext.NewsSections
                              where obj.Id == Id
                              select obj;

            sylvainDataContext.NewsSections.DeleteAllOnSubmit(NewsRow);
            sylvainDataContext.SubmitChanges();
        }
        /// <summary>
        /// Edit Record in News Section
        /// </summary>
        /// <param name="objNewsSection">Object of NewsSection</param>
        public void EditNewsSection(NewsSection objNewsSection)
        {
            NewsSection updateNews = sylvainDataContext.NewsSections.FirstOrDefault(u => u.Id == objNewsSection.Id);
            if (updateNews != null)
            {

                updateNews.Description = objNewsSection.Description;
                updateNews.Title = objNewsSection.Title;
                updateNews.UpdatedDate = objNewsSection.UpdatedDate;

            };

            sylvainDataContext.SubmitChanges();
        }
        /// <summary>
        /// Get Latest Quote of the day
        /// </summary>
        /// <param name="tagid">Tag Id</param>
        /// <returns>Latest Quote</returns>
        public NewsSection GetLatestQuote(int tagid)
        {
            var objItem = sylvainDataContext.NewsSections.Where(x => x.Tag == tagid);
            if (objItem.Count() > 0)
            {
                return objItem.OrderByDescending(x => x.CreatedDate).First();
            }
            else
            {
                return new NewsSection();
            }

        }  
        /// <summary>
        /// Get Latest Press Release
        /// </summary>
        /// <param name="tagid">Tag Id</param>
        /// <returns>Latest Press Release</returns>
        public NewsSection GetLatestPressRelease(int tagid)
        {
            var objItem = sylvainDataContext.NewsSections.Where(x => x.Tag == tagid);
            if (objItem.Count() > 0)
            {
                return objItem.OrderByDescending(x => x.CreatedDate).First();
            }
            else { return new NewsSection(); }
        }  
        /// <summary>
        /// Get Latest News
        /// </summary>
        /// <param name="tagid">Tag Id</param>
        /// <returns>Latest News</returns>
        public NewsSection GetLatestNews(int tagid)
        {
            var objItem = sylvainDataContext.NewsSections.Where(x => x.Tag == tagid);
            if (objItem.Count() > 0)
            {
                return objItem.OrderByDescending(x => x.CreatedDate).First();
            }
            else { return new NewsSection(); }
        }
        /// <summary>
        /// Description for Search Keyword
        /// </summary>
        /// <param name="strSearch">Search Keyword</param>
        /// <returns>List of Description for Keyword</returns>
        public List<NewsSection> GetSearchDetails(string strSearch)
        {
            return sylvainDataContext.NewsSections.Where(x => x.Description.Contains(strSearch)).ToList();
        
        }
        #endregion


        #region Employees Section
        /// <summary>
        /// Display all Records for NewsSection in Gridview
        /// </summary>
        /// <returns>List of News</returns>
        public List<EmployeesSection> GetAllEmployeesSection()
        {
            return sylvainDataContext.EmployeesSections.OrderByDescending(x => x.CreatedDate).ToList();
        }

        /// <summary>
        /// Display all Records for Employees in Gridview
        /// </summary>
        /// <returns>List of News</returns>
        public List<Employee> GetAllEmployees()
        {
            List<Employee> objList = sylvainDataContext.Employees.OrderByDescending(x => x.CreatedDate).ToList();
            return objList.OrderBy(x => x.Approved).ToList();
        }

        /// <summary>
        /// Add Record in News Section 
        /// </summary>
        /// <param name="objNewsSection">Object of NewsSection</param>
        public void AddEmployeesSection(EmployeesSection objEmployeesSection)
        {
            sylvainDataContext.EmployeesSections.InsertOnSubmit(objEmployeesSection);
            sylvainDataContext.SubmitChanges();
        }

        /// <summary>
        /// Add Record in Employees 
        /// </summary>
        /// <param name="objNewsSection">Object of NewsSection</param>
        public void AddEmployee(Employee objEmployee, string net_username, string net_password, string host)
        {
            Employee updateEmployees = sylvainDataContext.Employees.FirstOrDefault(u => u.UserName == objEmployee.UserName);
            if (updateEmployees != null)
            {
                throw new Exception(objEmployee.UserName + " already exists in the system. Please enter unique Username");
            }
            else
            {
                updateEmployees = sylvainDataContext.Employees.FirstOrDefault(u => u.Email == objEmployee.Email);
                if (updateEmployees != null)
                {
                    throw new Exception("Email Id already exists. Please enter unique Email Id.");
                }
                else
                {
                    sylvainDataContext.Employees.InsertOnSubmit(objEmployee);
                    sylvainDataContext.SubmitChanges();

                    try
                    {
                        if (objEmployee != null && objEmployee.Approved && host != null)
                        {
                            string body =
            @"
Your account have been created by Administrator.

Please log in with below information.

User Name: {0}

Password: {1}


Best Regards;

Sylvain Analytics Administrator ";

                            body = body + "\n Log in : {2}";
                            body = string.Format(body, objEmployee.UserName, objEmployee.Password, "http://sylvainanalytics.com/Employees.aspx");

                            //SMTP Configuration for Hotmail
                            SmtpClient smtpClient = new SmtpClient();
                            smtpClient.Credentials = new System.Net.NetworkCredential(net_username, net_password);
                            smtpClient.Host = host;
                            smtpClient.Port = 25;
                            smtpClient.EnableSsl = true;

                            MailMessage mailMessage = new MailMessage();

                            mailMessage.To.Add(objEmployee.Email);

                            mailMessage.Subject = "Registration Notification";
                            mailMessage.Body = body;

                            mailMessage.From = new MailAddress("Info@sylvainanalytics.com");
                            mailMessage.IsBodyHtml = false;
                            smtpClient.Send(mailMessage);
                        }
                    }
                    catch { }
                }
            }
        }

        /// <summary>
        /// Delete Record in News Section
        /// </summary>
        /// <param name="Id">News Id</param>
        public void DeleteEmployeesSection(int Id)
        {
            var EmployeesRow = from obj in sylvainDataContext.EmployeesSections
                          where obj.Id == Id
                          select obj;

            sylvainDataContext.EmployeesSections.DeleteAllOnSubmit(EmployeesRow);
            sylvainDataContext.SubmitChanges();
        }

        /// <summary>
        /// Delete Record in News Section
        /// </summary>
        /// <param name="Id">News Id</param>
        public void DeleteEmployee(int Id)
        {
            var EmployeesRow = from obj in sylvainDataContext.Employees
                               where obj.Id == Id
                               select obj;

            sylvainDataContext.Employees.DeleteAllOnSubmit(EmployeesRow);
            sylvainDataContext.SubmitChanges();
        }

        /// <summary>
        /// Edit Record in News Section
        /// </summary>
        /// <param name="objNewsSection">Object of NewsSection</param>
        public void EditEmployeesSection(EmployeesSection objEmployeesSection)
        {
            EmployeesSection updateEmployees = sylvainDataContext.EmployeesSections.FirstOrDefault(u => u.Id == objEmployeesSection.Id);
            if (updateEmployees != null)
            {

                updateEmployees.Description = objEmployeesSection.Description;
                updateEmployees.Title = objEmployeesSection.Title;
                updateEmployees.UpdatedDate = objEmployeesSection.UpdatedDate;

            };

            sylvainDataContext.SubmitChanges();
        }


        public Employee GetLogin(string username, string password)
        {
            Employee updateEmployees = sylvainDataContext.Employees.FirstOrDefault(u => u.UserName == username && u.Approved == true);
            if (updateEmployees != null)
            {
                updateEmployees = sylvainDataContext.Employees.FirstOrDefault(u => u.UserName == username && u.Password == password && u.Approved == true);
                if (updateEmployees != null)
                {
                    return updateEmployees;
                }
                else
                {
                    throw new Exception("Invalid Password. Please try again.");
                }
            }
            else
            {
                throw new Exception("Invalid Username. Please try again.");
            }
        }

        /// <summary>
        /// Edit Record in News Section
        /// </summary>
        /// <param name="objNewsSection">Object of NewsSection</param>
        public void EditEmployee(Employee objEmployee, string net_username, string net_password, string host)
        {
            Employee updateEmployees = sylvainDataContext.Employees.FirstOrDefault(u => u.UserName == objEmployee.UserName && u.Id != objEmployee.Id);
            if (updateEmployees != null)
            {
                throw new Exception("Username is already exists. Please try another Username.");
            }
            else
            {
                updateEmployees = sylvainDataContext.Employees.FirstOrDefault(u => u.Email == objEmployee.Email && u.Id != objEmployee.Id);
                if (updateEmployees != null)
                {
                    throw new Exception("Email Id is already exists. Please try another Email Id.");
                }
                else
                {
                    updateEmployees = sylvainDataContext.Employees.FirstOrDefault(u => u.Id == objEmployee.Id);
                    bool isApproved = updateEmployees.Approved;
                    if (updateEmployees != null)
                    {
                        updateEmployees.FirstName = objEmployee.FirstName;
                        updateEmployees.LastName = objEmployee.LastName;
                        updateEmployees.UserName = objEmployee.UserName;
                        updateEmployees.Password = objEmployee.Password;
                        updateEmployees.Email = objEmployee.Email;
                        updateEmployees.Title = objEmployee.Title;
                        updateEmployees.Location = objEmployee.Location;
                        updateEmployees.Phone = objEmployee.Phone;
                        updateEmployees.Mobile = objEmployee.Mobile;
                        updateEmployees.IsActice = objEmployee.IsActice;
                        updateEmployees.Approved = objEmployee.Approved;
                        updateEmployees.UpdatedDate = objEmployee.UpdatedDate;
                    };

                    sylvainDataContext.SubmitChanges();
                    try
                    {
                        if (updateEmployees != null && updateEmployees.Approved)
                        {
                            string body =
            @"
Your account have been approved.

Please log in with below information.

User Name: {0}

Password: {1}


Best Regards;

Sylvain Analytics Administrator ";

                            body = body + "\n Log in : {2}";
                            body = string.Format(body, updateEmployees.UserName, updateEmployees.Password, "http://sylvainanalytics.com/Employees.aspx");

                            //SMTP Configuration for Hotmail
                            SmtpClient smtpClient = new SmtpClient();
                            smtpClient.Credentials = new System.Net.NetworkCredential(net_username, net_password);
                            smtpClient.Host = host;
                            smtpClient.Port = 25;
                            smtpClient.EnableSsl = true;

                            MailMessage mailMessage = new MailMessage();

                            mailMessage.To.Add(updateEmployees.Email);

                            mailMessage.Subject = "Registration Notification";
                            mailMessage.Body = body;

                            mailMessage.From = new MailAddress("Info@sylvainanalytics.com");
                            mailMessage.IsBodyHtml = false;
                            smtpClient.Send(mailMessage);
                        }
                    }
                    catch { }
                }
            }
        }

        public void ForgotPassword(string email, string username, string net_username, string net_password, string host)
        {
            Employee updateEmployees = sylvainDataContext.Employees.FirstOrDefault(u => u.UserName == username && u.Email == email);
            if (updateEmployees != null)
            {                
                string body =
            @"
Please log in with below information.

User Name: {0}

Password: {1}


Best Regards;

Sylvain Analytics Administrator ";


                body = body + "\n Log in : {2}";
                body = string.Format(body, updateEmployees.UserName, updateEmployees.Password, "http://sylvainanalytics.com/Employees.aspx");


                //SMTP Configuration for Hotmail
                SmtpClient smtpClient = new SmtpClient();
                smtpClient.Credentials = new System.Net.NetworkCredential(net_username, net_password);
                smtpClient.Host = host;
                smtpClient.Port = 25;
                smtpClient.EnableSsl = true;

                MailMessage mailMessage = new MailMessage();

                mailMessage.To.Add(email);

                mailMessage.Subject = "Password Recovery";
                mailMessage.Body = body;

                mailMessage.From = new MailAddress("Info@sylvainanalytics.com");
                mailMessage.IsBodyHtml = false;
                smtpClient.Send(mailMessage);
            }
            else
            {
                throw new Exception("Email Id and Username does not exists in the system. Please enter valid Email Id and Username.");
            }
        }

        /// <summary>
        /// Get Latest Quote of the day
        /// </summary>
        /// <param name="tagid">Tag Id</param>
        /// <returns>Latest Quote</returns>
        public EmployeesSection GetLatestCEOCorner(int tagid)
        {
            var objItem = sylvainDataContext.EmployeesSections.Where(x => x.Tag == tagid);
            if (objItem.Count() > 0)
            {
                return objItem.OrderByDescending(x => x.CreatedDate).First();
            }
            else
            {
                return new EmployeesSection();
            }

        }
        /// <summary>
        /// Get Latest Press Release
        /// </summary>
        /// <param name="tagid">Tag Id</param>
        /// <returns>Latest Press Release</returns>
        public EmployeesSection GetLatestWhatNew(int tagid)
        {
            var objItem = sylvainDataContext.EmployeesSections.Where(x => x.Tag == tagid);
            if (objItem.Count() > 0)
            {
                return objItem.OrderByDescending(x => x.CreatedDate).First();
            }
            else { return new EmployeesSection(); }
        }
        
        /// <summary>
        /// Description for Search Keyword
        /// </summary>
        /// <param name="strSearch">Search Keyword</param>
        /// <returns>List of Description for Keyword</returns>
        public List<EmployeesSection> GetEmployeesSearchDetails(string strSearch)
        {
            return sylvainDataContext.EmployeesSections.Where(x => x.Description.Contains(strSearch)).ToList();

        }
        #endregion



        #region Publications

        /// <summary>
        /// Display all Records for Publication in Gridview
        /// </summary>
        /// <returns>List of Publications</returns>
        public List<Publication> GetAllPublications()
        {
            List<Publication> lstPub = sylvainDataContext.Publications.OrderByDescending(x => x.DateCreated).ToList();
            if (lstPub != null && lstPub.Count > 0)
            {
                lstPub.RemoveAt(0);
                if (lstPub.Count > 5)
                   lstPub = lstPub.Take(5).ToList();
            }
            return lstPub;
        }

        /// <summary>
        /// Add Record in Publication
        /// </summary>
        /// <param name="objPublication">Object of Publication</param>
        public void AddPublication(Publication objPublication)
        {
            sylvainDataContext.Publications.InsertOnSubmit(objPublication);
            sylvainDataContext.SubmitChanges();
        }

        /// <summary>
        /// Delete Record in Publication
        /// </summary>
        /// <param name="Id">Publication Id</param>
        public void DeletePublication(int Id)
        {
            var PublicationRow = from obj in sylvainDataContext.Publications
                          where obj.PublicationsId == Id
                          select obj;

            sylvainDataContext.Publications.DeleteAllOnSubmit(PublicationRow);
            sylvainDataContext.SubmitChanges();
        }

        /// <summary>
        /// Get Publication
        /// </summary>
        /// <param name="Id">Publication Id</param>
        public Publication GetPublication(int Id)
        {
            return sylvainDataContext.Publications.FirstOrDefault(u => u.PublicationsId == Id);
        }

        /// <summary>
        /// Edit Record in Publication
        /// </summary>
        /// <param name="objPublication">Object of Publication</param>
        public void EditPublication(Publication objPublication)
        {
            Publication updatePublication = sylvainDataContext.Publications.FirstOrDefault(u => u.PublicationsId == objPublication.PublicationsId);
            if (updatePublication != null)
            {
                updatePublication.PublicationsId = objPublication.PublicationsId;
                updatePublication.PageDescription = objPublication.PageDescription;
                updatePublication.UpdatedBy = objPublication.UpdatedBy;
                if (objPublication.PubFileName != "")
                    updatePublication.PubFileName = objPublication.PubFileName;
            };

            sylvainDataContext.SubmitChanges();
        }
       
        /// <summary>
        /// Get Current Publication
        /// </summary>
        /// <returns>Latest Publication</returns>
        public Publication GetCurrentPublication()
        {
            var objItem = sylvainDataContext.Publications.OrderByDescending(x => x.DateCreated);
            if (objItem != null && objItem.Count()>0)
            {
                return objItem.First();
            }
            else { return new Publication(); }
        }
        
        #endregion
    }
}
